/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Customer;
import model.Order;
import model.Orderitem;

/**
 *
 * @author Thai Ha Cong
 */
public class TransactionDAO {

    private Connection con;

    public TransactionDAO() {
        con = DBFactory.createConnection("onlineshopping");
    }

    public Integer createOrder(Order order) {
        try {
            Customer customer = order.getCustomer();
            Integer idCustomer = customer.getIdCustomer();

            if (idCustomer == null) {
                //insert customer
                String sqlst = "INSERT INTO customer(firstName, lastName, address, phone, dob, email) "
                        + "VALUE(?, ?, ?, ?, ?, ?)";

                PreparedStatement ps = con.prepareStatement(sqlst);

                ps.setString(1, customer.getFirstName());
                ps.setString(2, customer.getLastName());
                ps.setString(3, customer.getAddress());
                ps.setString(4, customer.getPhone());
                ps.setDate(5, new java.sql.Date(customer.getDob().getTime()));
                ps.setString(6, customer.getEmail());

                ps.executeUpdate();

                //get customer id
                String sqlst2 = "SELECT LAST_INSERT_ID()";
                PreparedStatement ps2 = con.prepareStatement(sqlst2);
                ResultSet rs2 = ps2.executeQuery();

                if (rs2.next()) {
                    idCustomer = rs2.getInt(1);
                }
            }

            //insert order
            Date currentTime = new Date();
            
            String sqlst3 = "INSERT INTO ordertb(createTime, totalQuantity, totalAmount, orderStatus, Customer_idCustomer) "
                    + "VALUE(NOW(), ?, ?, ?, ?)";
            PreparedStatement ps3 = con.prepareStatement(sqlst3);
//            ps3.setDate(1, new java.sql.Date(currentTime.getTime()));
            ps3.setInt(1, order.getTotalQuantity());
            ps3.setDouble(2, order.getTotalAmount());
            ps3.setString(3, order.getOrderStatus());
            ps3.setInt(4, idCustomer);

            ps3.executeUpdate();

            //get orderid
            Integer idOrder = 0;

            String sqlst4 = "SELECT LAST_INSERT_ID()";
            PreparedStatement ps4 = con.prepareStatement(sqlst4);
            ResultSet rs4 = ps4.executeQuery();

            if (rs4.next()) {
                idOrder = rs4.getInt(1);
            }

            //insert orderitems
            for (Orderitem oi : order.getOrderitemList()) {
                String sqlst5 = "INSERT INTO orderitem(orderQuantity, orderPrice, Order_idOrder, Product_idProduct) "
                        + "VALUE(?, ?, ?, ?)";
                PreparedStatement ps5 = con.prepareStatement(sqlst5);
                ps5.setInt(1, oi.getOrderQuantity());
                ps5.setDouble(2, oi.getOrderPrice());
                ps5.setInt(3, idOrder);
                ps5.setInt(4, oi.getProduct().getIdProduct());

                ps5.executeUpdate();
            }

            return idOrder;
        } catch (SQLException ex) {
            Logger.getLogger(TransactionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }
    
    public Integer createOrderWithPaypal(Order order) {
        try {
            Customer customer = order.getCustomer();
            Integer idCustomer = customer.getIdCustomer();

            if (idCustomer == null) {
                idCustomer = getCustomerIDFromEmail(customer.getEmail());
            }
            
            if (idCustomer == null) {
                //insert customer
                String sqlst = "INSERT INTO customer(firstName, lastName, address, phone, dob, email) "
                        + "VALUE(?, ?, ?, ?, ?, ?)";

                PreparedStatement ps = con.prepareStatement(sqlst);

                ps.setString(1, customer.getFirstName());
                ps.setString(2, customer.getLastName());
                ps.setString(3, customer.getAddress());
                ps.setString(4, customer.getPhone());
                ps.setDate(5, new java.sql.Date(customer.getDob().getTime()));
                ps.setString(6, customer.getEmail());

                ps.executeUpdate();

                //get customer id
                String sqlst2 = "SELECT LAST_INSERT_ID()";
                PreparedStatement ps2 = con.prepareStatement(sqlst2);
                ResultSet rs2 = ps2.executeQuery();

                if (rs2.next()) {
                    idCustomer = rs2.getInt(1);
                }
            }

            //insert order
            Date currentTime = new Date();
            
            String sqlst3 = "INSERT INTO ordertb(createTime, totalQuantity, totalAmount, orderStatus,"
                    + " Customer_idCustomer, paypalTransactionId, paypalPaymentStatus, paypalReasonCode) "
                    + "VALUE(NOW(), ?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement ps3 = con.prepareStatement(sqlst3);
//            ps3.setDate(1, new java.sql.Date(currentTime.getTime()));
            ps3.setInt(1, order.getTotalQuantity());
            ps3.setDouble(2, order.getTotalAmount());
            ps3.setString(3, order.getOrderStatus());
            ps3.setInt(4, idCustomer);
            ps3.setString(5, order.getPaypalTransactionId());
            ps3.setString(6, order.getPaypalPaymentStatus());
            ps3.setString(7, order.getPaypalReasonCode());

            ps3.executeUpdate();

            //get orderid
            Integer idOrder = 0;

            String sqlst4 = "SELECT LAST_INSERT_ID()";
            PreparedStatement ps4 = con.prepareStatement(sqlst4);
            ResultSet rs4 = ps4.executeQuery();

            if (rs4.next()) {
                idOrder = rs4.getInt(1);
            }

            //insert orderitems
            for (Orderitem oi : order.getOrderitemList()) {
                String sqlst5 = "INSERT INTO orderitem(orderQuantity, orderPrice, Order_idOrder, Product_idProduct) "
                        + "VALUE(?, ?, ?, ?)";
                PreparedStatement ps5 = con.prepareStatement(sqlst5);
                ps5.setInt(1, oi.getOrderQuantity());
                ps5.setDouble(2, oi.getOrderPrice());
                ps5.setInt(3, idOrder);
                ps5.setInt(4, oi.getProduct().getIdProduct());

                ps5.executeUpdate();
            }

            return idOrder;
        } catch (SQLException ex) {
            Logger.getLogger(TransactionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }
    
    public Integer getCustomerIDFromEmail(String email) {
        Integer result = null;
        
        try {
            String sqlst = "SELECT idCustomer FROM customer WHERE email = ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, email);
            
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(TransactionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return result;
    }
}
